/**
 * @author weiqing.hk
 * @date 2024年4月31日 下午11:51:38
 */
package com.xuelangyun.form.stat.controller;

import java.net.URLDecoder;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import javax.servlet.http.HttpServletResponse;

import com.xuelangyun.form.stat.service.IStatSqlService;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.xuelangyun.form.common.utils.R;
import com.xuelangyun.form.common.utils.Excel.ExcelUtils;
import com.xuelangyun.form.common.utils.Excel.HlSheet;

import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import io.swagger.annotations.ApiParam;

/**
 * 统计入口
 *
 * @author weiqing.hk
 * @date 2024年4月31日 下午11:51:38
 */
@RestController
@RequestMapping("stat")
@Api(value = "基于数据库sql的查询接口", tags = {"基于数据库sql的查询接口-通用"})
public class StatSqlController {

    Logger logger = LoggerFactory.getLogger(getClass());

    @Autowired
    private IStatSqlService sqlService;

    //@SysLog("基于sql查询数据")
    @RequestMapping(value = "/selectSql/{id}", method = {RequestMethod.GET, RequestMethod.POST})
    @ApiOperation("基于sql查询数据")
    public R<Object> selectSql(@ApiParam(name = "id", value = "定制sqlID", required = true) @PathVariable("id") String id, @ApiParam(name = "params", value = "查询参数,可为空", required = false) @RequestBody Object[] params) {
        logger.info("select sql ,id:" + id + " , params : " + JSON.toJSONString(params));
        return sqlService.select(id, params);
    }

    //@SysLog("基于数据库函数查询数据")
    @RequestMapping(value = "/selectFunction/{function}", method = {RequestMethod.GET, RequestMethod.POST})
    @ApiOperation("基于数据库函数查询数据")
    public R<Object> selectFunction(@ApiParam(name = "function", value = "函数名称", required = true) @PathVariable("function") String function, @ApiParam(name = "params", value = "查询参数,可为空", required = false) @RequestBody Object[] params) {
        logger.info("select function sql ,function:" + function + " , params : " + JSON.toJSONString(params));
        return sqlService.selectByFunction(function, params);
    }

    //@SysLog("基于数据库函数导出数据excel数据")
    @GetMapping(value = "/exportByFunction/{function}") //exportFunction/report_zhongce_defineData
    @ApiOperation("基于数据库函数导出数据")
    public void exportByFunction(@ApiParam(name = "function", value = "函数名称", required = true) @PathVariable String function, @ApiParam(name = "params", value = "查询参数,可为空", required = false) String params, @ApiParam(name = "filename", value = "文件名称", required = false) String filename, HttpServletResponse response) {

        logger.info("export function sql ,function: report_zhongce_defineData  , params : " + JSON.toJSONString(params));
        // 约定 这种导出函数返回的结构为json 包含columns-excel表头文字 , list 结构内容 填充到具体行,分号分隔字符串

        @SuppressWarnings("deprecation") String decode = URLDecoder.decode(params);
        String[] ps = {decode};
        // 查询数据
        String data = sqlService.selectFunction(function, ps);
        JSONObject jo = JSON.parseObject(data);
        List<HlSheet> sheets = new ArrayList<>();
        // 循环判断
        for (int i = 1; i < 10; i++) {

            if (!jo.containsKey("column" + i)) {
                break;
            }
            JSONArray array = jo.getJSONArray("list" + i);
            String columns = jo.getString("column" + i);
            String sheetName = jo.getString("sheet" + i);

            // 写入表头

            // 数据
            Iterator<Object> iters = array.iterator();
            HlSheet sheet = new HlSheet(sheetName, columns.split(";"));

            while (iters.hasNext()) {
                Object iter = iters.next();
                if (iter != null) {
                    String[] ss = iter.toString().split(";");
                    sheet.addLine(ss);
                }
            }
            sheets.add(sheet);
        }

        HlSheet[] ss = sheets.toArray(new HlSheet[0]);
        ExcelUtils.writeExcel(response, filename, ss);
    }

}
